/**
 * 
 */
package dstation.model;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;

import dstation.databeans.ResultBean;
import dstation.databeans.Reading;
import dstation.databeans.ResultDisplayBean;
import dstation.databeans.TestBean;
import dstation.testbeans.RFTestBean;

/**
 * @author Evan
 *
 */
public class ResultDAO {
	private static String insertString;
	private static String lookupString;
	private static String robotString;
	private static final Random random= new Random(System.currentTimeMillis());
	private static String testString;

	/**
	 * Create a new Result Data Access Object
	 * @param tableName The name of the Result table
	 * @param robotTable The name of the Robot table
	 * @param testTable The name of the Test table
	 * @param sensorTable The name of the Sensor table
	 */
	public ResultDAO(String tableName, String robotTable, String testTable, String sensorTable){
		insertString = "insert into "+tableName+" (robotid, testid, sensorid, data)" +
		" values ( ?, ?, ?, ?)";
		lookupString = "select * from "+tableName+" where resultid = ?";
		//TODO use the table names!
		robotString = "select test.name,sensor.name,result.date,robot.name,result.resultid from "+tableName+
		" left join test on result.testid = test.testid" +
		" left join sensor on result.sensorid = sensor.sensorid" +
		" left join robot on result.robotid = robot.robotid";
		testString = "select resultid from "+tableName+" where testid = ?";
		
		try {
			Connection con = DriverManager.getConnection("jdbc:mysql:///colony");
			ResultSet tableNames = con.prepareStatement("SHOW TABLES").executeQuery();
			boolean exists = false;
			while(tableNames.next()){
				String temp = tableNames.getString(1);
				if(temp.equals(tableName)){
					exists = true;
					break;
				}
			}
			if(!exists){
				Statement stmt = con.createStatement();
				stmt.executeUpdate("create table "+tableName+" (resultid int not null auto_increment, " +
						"robotid int, testid int, " +
						"sensorid int, date timestamp, data blob not null, " +
						"primary key(resultid), " +
						"foreign key(robotid) references "+robotTable+"(robotid), " +
						"foreign key(testid) references "+testTable+"(testid), " +
						"foreign key(sensorid) references "+sensorTable+"(sensorid))");
				stmt.close();
			}
			tableNames.close();
			con.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Insert results into the table
	 * @param r The results (Date field will be ignored)
	 * @return The inserted results
	 * @throws SQLException 
	 * @throws IOException 
	 * @throws ClassNotFoundException 
	 */
	public ResultBean insert(ResultBean r) throws SQLException, IOException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(insertString,Statement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, r.getRobotid());
			stmt.setInt(2, r.getTestid());
			stmt.setInt(3, r.getSensorid());

			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(r.getData());
			// serialize the employee object into a byte array
			byte[] dataAsBytes = baos.toByteArray();
			ByteArrayInputStream bais = 
				new ByteArrayInputStream(dataAsBytes);
			// bind our byte array  to the emp column
			stmt.setBinaryStream(4,bais, dataAsBytes.length);

			stmt.executeUpdate();
			con.commit();
			
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			r.setResultid(rs.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (IOException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}		
		return lookup(r,false);
	}

	/**
	 * Get a result set from the database
	 * @param r A bean containing the robot id, test id and sensor id desired
	 * @return The results
	 * @throws SQLException 
	 * @throws IOException 
	 * @throws ClassNotFoundException 
	 */
	public ResultBean lookup(ResultBean r, boolean getData) throws SQLException, IOException, ClassNotFoundException {
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(lookupString);
			stmt.setInt(1, r.getResultid());
			ResultSet rs = stmt.executeQuery();
			con.commit();
			rs.next();
			r.setDate(rs.getDate("date"));
			//			Object x = new ObjectInputStream(rs.getBlob("data").getBinaryStream()).readObject();
			//			if(x instanceof ArrayList){
			//				r.setData((ArrayList<Reading>)x);
			//			}
			if(getData){
				byte[] st = (byte[])rs.getObject("data");
				//   or  byte[] st = rs.getBytes(1);
				//   or  Blob aBlob = rs.getBlob(1);
				//       byte[] st = aBlob.getBytes(0, (int) aBlob.length());
				ByteArrayInputStream baip = 
					new ByteArrayInputStream(st);
				ObjectInputStream ois = 
					new ObjectInputStream(baip);
				// re-create the object
				r.setData( (ArrayList<Reading>) ois.readObject());
			}
			r.setRobotid(rs.getInt("robotid"));
			r.setTestid(rs.getInt("testid"));
			r.setSensorid(rs.getInt("sensorid"));
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (IOException e) {
			e.printStackTrace();
			throw e;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}

		}
		return r;
	}

	
	
	public List<ResultDisplayBean> getRobotTests(int id, int numResults, 
			String robotName, String testName, String sensorName) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		List<ResultDisplayBean> list = new ArrayList<ResultDisplayBean>();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			String queryString = robotString;
			if(id!=-1){
				queryString += " where result.robotid = ?";
			}
			if(robotName != null){
				queryString += " where robot.name = ?";
			}
			if(testName != null){
				queryString += " where test.name = ?";
			}
			if(sensorName != null){
				queryString += " where sensor.name = ?";
			}
			queryString+=" order by result.date desc";
			stmt = con.prepareStatement(queryString,Statement.RETURN_GENERATED_KEYS);
			int i = 1;
			if(id!=-1){
				stmt.setInt(i, id);
				i++;
			}
			if(robotName != null){
				stmt.setString(i, robotName);
				i++;
			}
			if(testName != null){
				stmt.setString(i, testName);
				i++;
			}
			if(sensorName != null){
				stmt.setString(i, sensorName);
				i++;
			}
			ResultSet rs = stmt.executeQuery();
			con.commit();
			while(rs.next() && (numResults == -1 ||list.size()<numResults)){
				ResultDisplayBean r = new ResultDisplayBean();
				r.setTestName(rs.getString(1));
				r.setSensorName(rs.getString(2));
				r.setDate(rs.getDate(3));
				r.setRobotName(rs.getString(4));
				r.setResultid(rs.getInt(5));
				list.add(r);
			}
			
		} catch (MySQLSyntaxErrorException e){
			list.clear();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return list;
	}

	public ResultBean runTest(ResultBean r, TestBean t) throws SQLException, IOException, ClassNotFoundException {
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(insertString,Statement.RETURN_GENERATED_KEYS);

			stmt.setInt(1, r.getRobotid());
			stmt.setInt(2, r.getTestid());
			stmt.setInt(3, r.getSensorid());
			populateRandom((RFTestBean) t.getMetadata(),r);
			
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(r.getData());
			// serialize the employee object into a byte array
			byte[] dataAsBytes = baos.toByteArray();
			ByteArrayInputStream bais = 
				new ByteArrayInputStream(dataAsBytes);
			// bind our byte array  to the emp column
			stmt.setBinaryStream(4,bais, dataAsBytes.length);

			stmt.executeUpdate();
			con.commit();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			r.setResultid(rs.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (IOException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return lookup(r,false);
	}
	
	public void populateRandom(RFTestBean t, ResultBean res){
		ArrayList<Reading> data = new ArrayList<Reading>(t.getNumReadings());
		int b = random.nextInt(11)-5;
		double m = random.nextGaussian()*.1 + 1;
		int span = (t.getEndDistance()-t.getStartDistance()+1)/t.getNumReadings();
		span = span == 0? 1:span;
		for(int index = t.getStartDistance(); index<=t.getEndDistance();index+=span){
			Reading x = res.createReading();
			x.realVal=index;
			x.robotVal=(int) Math.round(m*index+b+random.nextInt(11));
			data.add(x);
		}
		res.setData(data);
	}

	public Boolean testHasNoResults(int testid) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(testString);
			stmt.setInt(1, testid);
			ResultSet rs = stmt.executeQuery();
			con.commit();
			return !rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
	}
}
